package dao.impl;

import dao.RoleDao;
import pojo.Role;
import util.BaseDao;
import util.PageBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 角色接口的实现类
 */
public class RoleDaoImpl implements RoleDao {

    /**
     *查询所有角色
     */
    @Override
    public List<Role> queryAllRole() {
        List<Role> roles=new ArrayList<Role>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            conn=BaseDao.getConnection();
            String sql="select * from role";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                Role role=new Role();
                role.setRoId(rs.getInt("ro_id"));
                role.setRoName(rs.getString("ro_name"));
                role.setRoDesc(rs.getString("ro_desc"));
                role.setRoRool(rs.getString("ro_rool"));
                roles.add(role);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return roles;
    }

    /**
     *根据管理员id查询管理员角色对象
     */
    @Override
    public Role queryOneRole(int id) {
        Role role=new Role();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=BaseDao.getConnection();
            String sql="select distinct ro.* from adross ur,role ro,admin ad " +
                    "where ro.ro_id=ur.role_id and ur.admin_id=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs=ps.executeQuery();
            while(rs.next()){
                role.setRoId(rs.getInt("ro_id"));
                role.setRoName(rs.getString("ro_name"));
                role.setRoDesc(rs.getString("ro_desc"));
                role.setRoRool(rs.getString("ro_rool"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return role;
    }

    /**
     *分页功能
     */
    @Override
    public PageBean rolePageList(PageBean pb, String ename) {
        List<Role> list=new ArrayList<Role>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=BaseDao.getConnection();
            String s="";
            if(null!=ename && !ename.equals("")){
                s+="and  ro_name LIKE '%"+ename+"%'";
            }
            String sql=" SELECT * FROM (" +
                    "  SELECT ROWNUM r,role.* FROM role " +
                    "  WHERE 1=1 "+s+" and ROWNUM<=?" +
                    " )WHERE r>?";
            ps=conn.prepareStatement(sql);
            //给占位符号进行赋值
            ps.setInt(1,pb.getPageIndex()*pb.getPageSize());
            ps.setInt(2,pb.getPageSize()*(pb.getPageIndex()-1));
            rs=ps.executeQuery();
            while(rs.next()){
                Role role=new Role();
                role.setRoId(rs.getInt("ro_id"));
                role.setRoName(rs.getString("ro_name"));
                role.setRoDesc(rs.getString("ro_desc"));
                role.setRoRool(rs.getString("ro_rool"));
                list.add(role);
            }
            pb.setList(list);

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return pb;
    }

    /**
     * 统计角色总数
     */
    @Override
    public int count(String ename) {
        int count=0;
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=BaseDao.getConnection();
            String s="";
            if(null!=ename && !ename.equals("")){
                s+="  and  ro_name LIKE '%"+ename+"%' ";
            }
            String sql="select count(*) from role  where 1=1 "+s;
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                count=rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return count;
    }

    /**
     *新增角色
     */
    @Override
    public int inseRole(Role role) {
        return BaseDao.cud("insert into role values(sequence_role.nextval,?,?,?)",role.getRoName(),role.getRoDesc(),role.getRoRool());
    }

    /**
     *根据角色名称查找角色id
     */
    @Override
    public int queryRoIdByName(String Name) {
        int id=0;
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=BaseDao.getConnection();
            String sql="select * from role WHERE ro_Name=?";
            ps=conn.prepareStatement(sql);
            ps.setString(1,Name);
            rs=ps.executeQuery();
            while(rs.next()){
                id=rs.getInt("ro_Id");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return id;
    }

    /**
     * 修改角色表的回显
     */
    @Override
    public Role queryRoleDesc(int id) {
        Role role=new Role();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            conn=BaseDao.getConnection();
            String sql="select * from role where ro_id=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs=ps.executeQuery();
            while(rs.next()){
                role.setRoId(rs.getInt("ro_id"));
                role.setRoName(rs.getString("ro_name"));
                role.setRoDesc(rs.getString("ro_desc"));
                role.setRoRool(rs.getString("ro_rool"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return role;
    }

    /**
     *修改角色
     */
    @Override
    public int modifRole(Role role) {
        return BaseDao.cud("update role set ro_desc=?,ro_rool=? where ro_id=?",role.getRoDesc(),role.getRoRool(),role.getRoId());
    }

    /**
     *删除角色
     */
    @Override
    public int deleRole(int id) {
        return BaseDao.cud("delete from role where ro_id=?",id);
    }
}
